Fixing Incorrect Data Types and Null Values in Tableau

Fixing incorrect data types and null values is an important part of data cleaning and preparation in Tableau. Data types determine how Tableau interprets, calculates, sorts, and visualizes information. Null values represent missing or unknown data and can affect calculations, filters, aggregations, and charts. By correcting data types and handling null values appropriately, analysts can improve data quality, produce accurate visualizations, and create reliable dashboards.

Course Link: Explore Tableau Training

Demo Link: Register for a Tableau Course Demo

1. Learning Objectives

After completing this topic, learners will be able to:

• Understand data types and null values in Tableau.
• Identify incorrect data types in datasets.
• Change field data types in Tableau Desktop.
• Convert text values into dates and numbers.
• Understand the difference between null, zero, blank, and missing records.
• Replace null values using calculated fields.
• Use IFNULL(), ISNULL(), ZN(), and related functions.
• Handle null values in dimensions, measures, dates, and calculations.
• Clean data using Tableau Prep and data source settings.
• Validate cleaned data before building dashboards.

2. What Are Data Types in Tableau?

A data type defines the kind of information stored in a field. Tableau uses data types to determine how a field can be used in calculations, sorting, filtering, aggregation, and visualization.

For example, a field containing sales amounts should generally use a numeric data type, while a field containing customer names should use a text data type.

Common Data Types in Tableau

Important: Geographic roles are metadata assigned to fields, not a separate basic storage type in the same sense as string, number, date, or Boolean.

3. What Are Incorrect Data Types?

An incorrect data type occurs when Tableau interprets a field differently from its intended meaning. This can happen when data is imported from Excel, CSV files, databases, or other sources with inconsistent formatting.

Examples of Incorrect Data Types

Why Incorrect Data Types Matter

• Numerical calculations may fail or produce unexpected results.
• Dates may sort alphabetically instead of chronologically.
• Identifiers may be aggregated instead of treated as categories.
• Joins and relationships may fail when matching fields use incompatible types.
• Maps may not recognize geographic fields correctly.
• Charts, filters, and calculated fields may produce misleading results.

4. How to Identify Incorrect Data Types

Method 1: Check the Data Pane

In Tableau Desktop, inspect the icons beside field names in the Data pane. The icons indicate whether Tableau recognizes a field as text, a date, a number, or another supported type.

1. Open the Tableau workbook.
2. Connect to the required data source.
3. Open the Data pane.
4. Review the icons beside important field names.
5. Compare the assigned type with the actual contents and intended use of each field.

Method 2: Inspect the Data Source Page

The Data Source page displays fields and sample values. Use it to check whether dates, numeric values, and text columns have been interpreted correctly.

1. Click the Data Source tab.
2. Review the column names and previewed records.
3. Identify columns with inconsistent values.
4. Check whether empty cells or mixed values are affecting type detection.
5. Change the data type where necessary.

Method 3: Test the Field in a Worksheet

Drag the field into a worksheet and observe its behavior. If a date is treated as ordinary text or a numeric field appears as a category, the field may have an unsuitable type.

Method 4: Review Calculations

Calculated fields may return errors when an expression uses incompatible data types. Review the formula, the types of its inputs, and the exact error message.

5. Changing Data Types in Tableau Desktop

Tableau allows you to change the data type of a source field from the Data Source page or the Data pane.

Method 1: Change the Type on the Data Source Page

1. Open the workbook and click the Data Source tab.
2. Locate the field that needs correction.
3. Click the data type icon beside the field name.
4. Select the appropriate type, such as String, Number (Whole), Number (Decimal), Date, or Date & Time.
5. Inspect the resulting preview values.
6. Open a worksheet and validate calculations and visualizations.

Method 2: Change the Type in the Data Pane

1. Open a worksheet.
2. Locate the field in the Data pane.
3. Right-click the field or click its data type icon.
4. Select Change Data Type.
5. Choose the required type.
6. Check whether the field works correctly in existing worksheets.

Best practice: Correct data types before creating extracts or building complex calculations. If a decimal field is interpreted as an integer, fractional precision may be lost during certain data preparation or extraction operations.

6. Converting Text to Numbers

Sometimes numeric data is imported as text because the source includes currency symbols, commas, spaces, or inconsistent formatting.

Example Dataset

If Sales is stored as a string, Tableau may not allow the expected numeric aggregation.

Option A: Change the Data Type

If the values are already valid numbers, change the field's type to Number (Whole) or Number (Decimal), depending on the data.

Option B: Use a Calculated Field

Create a calculated field named Sales as Number:

FLOAT([Sales])

This converts values that Tableau can interpret as numeric into decimal numbers.

For whole-number values, you can use:

INT([Quantity])

Important: INT() converts a value to an integer and can discard its fractional component. Do not use it for money or measurements that require decimal precision.

Handling Currency Symbols and Commas

If a text value contains formatting characters, clean the string first. For example, a field containing values such as ₹15,000 may require removing the currency symbol and thousands separator before conversion.

FLOAT(
    REPLACE(
        REPLACE([Sales Text], "₹", ""),
        ",",
        ""
    )
)

This example assumes that the input format is consistent and that the remaining string is valid for the numeric conversion. If the source uses different decimal separators or currency formats, standardize them carefully before converting.

7. Converting Text to Dates

Dates may be imported as strings when the source contains inconsistent formats or Tableau cannot automatically recognize the date pattern.

Example

Option A: Change the Field Type

If Tableau recognizes the date values, change the field type to Date or Date & Time.

Option B: Use a Date Conversion Function

Create a calculated field named Order Date Converted:

DATE([Order Date Text])

Use this approach when Tableau can interpret the text as a date. If the source format is ambiguous or inconsistent, clean or standardize it in the source or Tableau Prep before conversion.

Extracting Date Parts

Once the field is a valid date, you can extract date components for analysis:

YEAR([Order Date])
MONTH([Order Date])
DAY([Order Date])

These expressions return the year, month number, and day of month respectively.

Important Date Validation

• Check whether the day and month are in the expected order.
• Confirm the correct regional date format.
• Identify impossible dates and inconsistent values.
• Check whether timestamps require Date & Time instead of Date.
• Verify that date filters and time-series charts sort chronologically.

8. What Are Null Values in Tableau?

A null value represents the absence of a valid value in a field. It can occur because information was not recorded, the source contains empty cells, a calculation returns no value, or a join does not find a matching record.

Null does not automatically mean zero, false, or an empty string. It means that a usable value is not present for that field.

Example Dataset

Rahul's salary is unknown in this example. Replacing it with zero would imply a known salary of zero, which may not be accurate.

9. Difference Between Null, Zero, Blank, and Missing Records

Important distinction: A null value exists in a row where a field has no valid value. A missing record means the row itself is absent. Replacing nulls with zero does not create rows that are missing from the source.

10. Common Causes of Null Values

10.1 Missing Information in the Source

A source file or database may contain empty fields because users did not enter the information.

10.2 Inconsistent Data Formats

When a column contains mixed types, Tableau may interpret some values as null. For example, a numeric column containing the text value Unknown may not be converted cleanly to a number.

10.3 Join or Relationship Mismatches

Nulls can appear when records in one table do not match records in another table. The cause may be different key formats, missing keys, extra spaces, or genuinely unmatched records.

10.4 Calculated Fields

A calculation may return null when the input is null or when a conditional expression does not return a value for the current record.

10.5 Invalid Date or Numeric Values

Text that cannot be interpreted as the intended data type may become null during type conversion, depending on the connector and data source.

10.6 Source-System Rules

Some systems use null to represent unknown, not applicable, not yet entered, or unavailable information. Understand the source system's meaning before cleaning the data.

11. How to Find Null Values in Tableau

Method 1: View Null Indicators

When a visualization contains null values, Tableau may display a null indicator. Depending on the view and field type, null values may also appear as a separate category or an empty mark.

1. Create a worksheet using the field you want to inspect.
2. Add the field to Rows, Columns, or the Marks card.
3. Look for null indicators or unexpected blank categories.
4. Inspect the underlying records to determine the cause.

Method 2: Use a Calculated Field

Create a calculated field named Salary Null Status:

IF ISNULL([Salary]) THEN
    "Missing Salary"
ELSE
    "Salary Available"
END

This separates records with null salaries from records that have a value.

Method 3: Filter for Null Values

For many fields, you can use the field's filter options to isolate null values. The available filter behavior depends on whether the field is a dimension, measure, date, or comes from a particular connector.

Method 4: Inspect the Data Source

Review the preview data and source records to determine whether the null originated in the source, during type conversion, or after joining tables.

12. Replacing Null Values with IFNULL()

The IFNULL() function returns the first expression when it is not null; otherwise, it returns a replacement expression. The replacement must be compatible with the original expression's data type.

Syntax

IFNULL(expression, replacement_value)

Example 1: Replace Null Salary with Zero

IFNULL([Salary], 0)

If Salary contains a null value, the calculation returns zero. Otherwise, it returns the original salary.

Example 2: Replace Null Department with a Label

IFNULL([Department], "Not Assigned")

This returns the department name when available and the text Not Assigned when the field is null.

Example 3: Replace Null Profit

IFNULL([Profit], 0)

This can be useful when the business rule explicitly treats missing profit as zero. If profit is genuinely unknown, however, replacing it with zero could distort the analysis.

When to Use IFNULL()

• When a field needs a defined replacement for null values.
• When a text field needs a consistent fallback label.
• When numeric calculations require a deliberate null-handling rule.
• When preparing fields for charts and dashboard labels.

13. Replacing Null Values with ZN()

The ZN() function returns zero when the expression is null. When the expression is not null, it returns the original value. It is primarily useful for numeric calculations.

Syntax

ZN(expression)

Example

ZN([Sales])

If Sales is null, the calculated field returns zero. Otherwise, it returns the existing sales value.

Using ZN() with Aggregated Sales

ZN(SUM([Sales]))

This can replace a null result from the aggregation with zero. The behavior depends on the calculation and view context.

IFNULL() vs. ZN()

14. Checking Null Values with ISNULL()

The ISNULL() function checks whether an expression is null and returns a Boolean result: TRUE or FALSE.

Syntax

ISNULL(expression)

Example

ISNULL([Customer Name])

This returns TRUE when Customer Name is null and FALSE when it contains a non-null value.

Creating a Data Quality Label

IF ISNULL([Customer Name]) THEN
    "Missing Customer"
ELSE
    "Valid Customer"
END

This calculated field can be used to count incomplete records or build a data-quality dashboard.

Important Difference

ISNULL() identifies nulls; it does not replace them. Use IFNULL() or another suitable expression when a replacement value is required.

15. Using IF and IIF to Handle Null Values

Conditional expressions can apply business rules when a value is missing. They are useful when different null cases need different outcomes.

Example 1: Classifying Missing Sales

IF ISNULL([Sales]) THEN
    "Sales Missing"
ELSE
    "Sales Available"
END

Example 2: Handling Missing Profit

IF ISNULL([Profit]) THEN
    0
ELSE
    [Profit]
END

Use this expression only when zero is an appropriate business interpretation of missing profit.

Example 3: Assigning a Default Category

IF ISNULL([Category]) THEN
    "Uncategorized"
ELSE
    [Category]
END

Example 4: Using IIF()

IIF(ISNULL([Sales]), 0, [Sales])

This returns zero for a null Sales value and the existing value otherwise. For complex logic, IF/THEN/ELSE expressions may be easier to read and maintain.

16. Handling Null Values in Calculations

Null values can affect calculations because mathematical operations involving null often return null. Analysts should decide whether to preserve missing values, replace them, or exclude the affected records.

Example 1: Total Sales

ZN(SUM([Sales]))

Returns zero if the aggregate result is null. This does not necessarily create missing records or missing date categories.

Example 2: Profit Margin

IF SUM([Sales]) = 0 OR ISNULL(SUM([Sales])) THEN
    NULL
ELSE
    SUM([Profit]) / SUM([Sales])
END

This calculation avoids dividing by zero and returns null when the sales denominator is missing or zero. It is often better to leave the margin unknown than to display a misleading result.

Example 3: Default Value for a Missing Discount

IFNULL([Discount], 0)

Use zero only if a missing discount means no discount under the organization's business rules.

Example 4: Check for Missing Dates

IF ISNULL([Order Date]) THEN
    "Date Missing"
ELSE
    "Date Available"
END

This helps identify records that cannot be assigned to the expected date-based analysis.

17. Handling Null Values in Dimensions and Measures

Null Values in Dimensions

Dimensions are commonly used to categorize data, such as Customer, Region, Department, and Product. A null dimension can appear as an unknown or missing category.

Example calculation:

IFNULL([Region], "Unknown Region")

This creates a display label for records with a null Region.

Null Values in Measures

Measures are numeric fields such as Sales, Profit, Quantity, and Cost. Null measures may affect totals, averages, percentages, and other calculations.

Example calculation:

ZN([Profit])

This returns zero for a null Profit value. Before applying it, determine whether zero is a meaningful replacement.

Comparison

18. Fixing Null Values in Tableau Prep

Tableau Prep is designed for data preparation and cleaning before analysis. It can help identify, standardize, replace, or remove problematic values in supported workflows.

Step 1: Connect to the Data

Open Tableau Prep Builder and connect to the relevant Excel, CSV, database, or other supported source.

Step 2: Add a Cleaning Step

Add a cleaning step to inspect field profiles, data distributions, data types, and null values.

Step 3: Review the Field Profile

Inspect the profile card for the field you want to clean. Identify nulls, inconsistent values, unexpected formats, and outliers.

Step 4: Correct the Data Type

Select the appropriate field type. Review the resulting values and check whether any records become null because they cannot be converted.

Step 5: Replace or Remove Values

Depending on the data and the desired outcome, you can replace selected values, standardize inconsistent text, filter records, or use calculated fields to define replacement rules.

Step 6: Validate the Result

Confirm that the cleaned field contains the expected types and values. Check row counts and inspect records that were modified or excluded.

Step 7: Output the Prepared Data

Configure the output and run the flow. Connect Tableau Desktop to the resulting data and validate the final analysis.

Important: Removing rows with null values may discard useful information. Always determine whether the record should be corrected, retained, flagged, or excluded before applying a cleaning rule.

19. Handling Null Values Caused by Joins

A join combines records from two tables based on a matching condition. When a record from one table has no match in the other table, nulls may appear in fields from the unmatched side, depending on the join type.

Example

Consider two tables: Customers and Orders.

Customer 103 has no matching order in the Orders table. If the join preserves all customers, Order ID may be null for Rahul.

How to Troubleshoot Join-Related Nulls

1. Confirm the join keys are correct.
2. Check that matching fields use compatible data types.
3. Look for extra spaces, different capitalization, or inconsistent ID formats.
4. Check for missing keys in either table.
5. Confirm the intended join type.
6. Inspect unmatched records before replacing nulls.

Important: Do not automatically replace unmatched order IDs or customer identifiers with zero. Investigate whether the absence of a match is expected and preserve the meaning of the data.

20. Handling Blank Strings and Whitespace

Text fields may contain empty strings or whitespace instead of true null values. These values can make data-quality checks unreliable because a simple null check will not necessarily identify them.

Example

Trim Leading and Trailing Spaces

Create a calculated field or clean the source text using:

TRIM([Customer Name])

This removes leading and trailing spaces. It does not automatically convert every empty string to a true null.

Standardize Blank Values

If the source uses blank text to represent missing information, create a field that classifies or replaces those values. For example:

IF ISNULL([Customer Name])
   OR TRIM([Customer Name]) = "" THEN
    "Missing Name"
ELSE
    TRIM([Customer Name])
END

This formula checks both null and empty or whitespace-only text and returns a consistent label. Use it only when the field is a string.

21. Best Practices for Fixing Data Types and Null Values

• Understand the source data: Inspect sample records before making changes.
• Choose the correct type: Use dates for dates, numeric types for measurements, and strings for identifiers where appropriate.
• Preserve precision: Do not convert decimal measurements to integers unless rounding or truncation is intended.
• Validate date formats: Consider regional formats and timestamp requirements.
• Investigate mixed values: Standardize inconsistent text and numeric formats before conversion.
• Distinguish null from zero: A missing amount is not automatically an amount of zero.
• Choose replacements carefully: Apply IFNULL() or ZN() only when the replacement matches the business meaning.
• Check joins: Investigate unmatched records before replacing nulls created by joins.
• Validate calculations: Test calculated fields with null, zero, and valid values.
• Check row counts: Confirm that cleaning has not unintentionally removed records.
• Document transformations: Record replacement rules and data type changes.
• Clean early: Where practical, standardize fields before building complex dashboards.

22. Common Mistakes to Avoid

23. Practical Project: Cleaning an Employee Dataset

Project Objective

Correct data types and handle missing values in an employee dataset before creating a Tableau dashboard.

Sample Dataset

Problems to Identify

• Employee ID should retain leading zeros and may be best stored as a string.
• Salary may be imported as text because of the value Unknown.
• Department contains an empty value.
• Joining Date contains a missing value.
• Salary includes a decimal value that should not lose precision.

Project Steps

1. Connect Tableau to the dataset.
2. Inspect the data types in the Data Source page.
3. Keep Employee ID as a string if preserving leading zeros is important.
4. Standardize the Salary field and determine how the text value Unknown should be handled.
5. Convert valid salary values to a suitable numeric type.
6. Convert Joining Date to Date after validating the date format.
7. Identify missing Department and Joining Date values.
8. Decide whether missing values should be flagged, replaced, or retained as nulls.
9. Create calculated fields to label missing values.
10. Validate salary totals, employee counts, and date-based analysis.
11. Create a dashboard that displays employee count, department distribution, and salary summaries.
12. Document the cleaning rules and the records requiring follow-up.

Example Calculated Fields

Salary Status:

IF ISNULL([Salary]) THEN
    "Salary Missing"
ELSE
    "Salary Available"
END

Department Display:

IF ISNULL([Department])
   OR TRIM([Department]) = "" THEN
    "Not Assigned"
ELSE
    TRIM([Department])
END

Salary for Analysis:

IF ISNULL([Salary]) THEN
    NULL
ELSE
    [Salary]
END

The salary example preserves missing values rather than automatically converting them to zero. In an actual project, first clean and convert the original salary field to a numeric type; then apply the desired null-handling rule.

Expected Outcome

• Fields have appropriate data types.
• Valid salaries are available for numeric analysis.
• Employee IDs retain their intended format.
• Missing departments and dates are clearly identified.
• Calculations and charts behave as expected.
• Cleaning decisions are documented and reproducible.

24. Interview Questions and Answers

Q1. What is a data type in Tableau?

Answer: A data type defines the kind of information stored in a field, such as text, numbers, dates, date-time values, or Boolean values. It influences how Tableau interprets and analyzes the field.

Q2. How can you change a field's data type in Tableau?

Answer: On the Data Source page or in the Data pane, select the field's data type icon or use Change Data Type, then choose the appropriate type and validate the result.

Q3. What is a null value?

Answer: A null represents the absence of a valid value. It is different from zero, an empty string, or a missing row.

Q4. What is the difference between IFNULL() and ISNULL()?

Answer: IFNULL() returns a specified replacement when its first expression is null. ISNULL() checks whether an expression is null and returns TRUE or FALSE.

Q5. What does ZN() do in Tableau?

Answer: ZN() returns zero when the supplied expression is null; otherwise, it returns the original expression. It is commonly used with numeric calculations.

Q6. Why should you not replace all null values with zero?

Answer: Zero represents a numeric value, while null represents missing or unknown information. Replacing unknown values with zero may distort totals, averages, ratios, and business conclusions.

Q7. How do you convert text into a number?

Answer: Change the field's type to a numeric type when the values are valid numbers, or use a conversion function such as FLOAT() or INT() in a calculated field. Clean currency symbols, separators, and invalid values first.

Q8. How do you convert a text field into a date?

Answer: Change the field's data type to Date or use DATE() when Tableau can interpret the input. Standardize ambiguous date formats and validate the resulting dates.

Q9. Why might a numeric column contain null values after conversion?

Answer: The column may contain text, invalid numeric formats, inconsistent separators, or other values that cannot be interpreted as numbers.

Q10. How can you identify null values?

Answer: Inspect the source preview, use Tableau's null indicators and filters, or create a calculated field using ISNULL().

Q11. What causes null values after a join?

Answer: A record may not find a matching key in the other table. Incorrect join fields, incompatible types, missing IDs, and inconsistent formatting can contribute to unmatched records.

Q12. How do you handle blank strings?

Answer: Use TRIM() to remove leading and trailing spaces and test whether the resulting string is empty. Handle nulls separately because an empty string is not necessarily a null.

Q13. What is the difference between Date and Date & Time?

Answer: Date represents a calendar date, while Date & Time includes both a date and a time component. Use Date & Time when time-of-day information is needed.

Q14. What is Tableau Prep used for in data cleaning?

Answer: Tableau Prep provides a visual environment for inspecting data, correcting data types, standardizing values, handling nulls, combining data, and preparing output for analysis.

Q15. How do you validate data after cleaning?

Answer: Check field types, sample values, row counts, null counts, totals, calculations, joins, and the resulting charts. Compare the cleaned output with the original source where possible.

25. Practical Exercises

1. Import a CSV file where Sales is stored as text and convert it into a numeric field.
2. Convert a string date into a valid Date field and verify chronological sorting.
3. Create a calculated field that labels null customer names.
4. Use IFNULL() to replace missing category values with a meaningful label.
5. Use ZN() to replace null numeric results with zero, then explain whether zero is appropriate for the scenario.
6. Use ISNULL() to calculate how many records have missing dates.
7. Identify empty strings and whitespace-only values in a text field.
8. Join Customers and Orders tables and investigate unmatched records.
9. Clean an employee dataset in Tableau Prep and compare the row counts before and after cleaning.
10. Create a data-quality dashboard showing missing values by field and department.

26. Quick Revision Checklist

• Understand the intended meaning of each field.
• Verify data types in the Data Source page and Data pane.
• Convert text to numbers only after cleaning invalid characters.
• Validate date formats and timestamps.
• Preserve decimal precision when needed.
• Keep identifiers as strings when leading zeros or categorical behavior matters.
• Distinguish nulls from zeros, blank strings, and missing records.
• Use IFNULL() for compatible replacement values.
• Use ZN() to replace null numeric expressions with zero when justified.
• Use ISNULL() to identify null values.
• Inspect join-related nulls before changing them.
• Validate calculations, totals, row counts, and visualizations after cleaning.
• Document the business rules used to replace or preserve missing values.

27. Official Tableau Learning Resources

• Tableau Data Types Documentation — learn how Tableau identifies and changes field data types.
• Show or Hide Missing Values and Empty Rows — understand the difference between null values and missing date or bin headers.
• Format Numbers and Null Values — learn about displaying or formatting null values in visualizations.
• Tableau Functions Reference — explore functions such as IFNULL(), ISNULL(), and ZN().
• Tableau Help and Support — find additional learning and troubleshooting resources.

28. Conclusion

Fixing incorrect data types and null values is a fundamental data preparation skill in Tableau. Correct data types ensure that fields behave as expected in calculations, filters, joins, and visualizations. Appropriate null handling prevents missing information from producing misleading business conclusions. Functions such as IFNULL(), ISNULL(), and ZN(), together with Tableau Desktop and Tableau Prep, help analysts identify and manage data-quality issues.

The most important rule is to understand why a value is missing before replacing it. A carefully cleaned dataset produces more reliable dashboards, better analysis, and greater confidence in business decisions.

Explore Tableau Training: Explore Tableau Training

Register for a Course Demo: Register for a Tableau Course Demo